from app.db import db
from app.Models.Todo.TodoModel import Todo  # 假设你的Todo模型在该路径
from typing import List, Dict, Optional
from datetime import date, datetime


class TodoDAO:
    """Todo清单DAO层（含外键关联查询）"""

    @staticmethod
    def add_todo(
            user_id: int,
            description: str,
            deadline: date,
            goal_id: int,
            self_score: int,
            importance: int,
            remark: str,
            is_done: int = 0,

            project_id: Optional[int] = None,
            attachment: Optional[str] = None,
            focus_time: int = 0
    ) -> (bool, str):
        """
        新增Todo任务
        :param user_id: 用户ID（外键，关联user_info.id）
        :param description: 任务描述
        :param deadline: 截止日期（date类型）
        :param goal_id: 目标ID（外键，关联goal.id，必传）
        :param self_score: 自我评分
        :param importance: 重要性（建议1-5分）
        :param is_done: 完成状态（0待完成/1完成，默认0）
        :param remark: 日志感想
        :param project_id: 项目ID（外键，关联program.id，可选）
        :param attachment: 附件路径（可选）
        :param focus_time: 专注时长（分钟，默认0）
        :return: (bool, 提示字符串)
        """
        try:
            # 1. 校验外键是否存在
            # 校验user_id
            user_exists = db.session.query(
                db.exists().where(db.Model.metadata.tables['user_info'].c.id == user_id)).scalar()
            if not user_exists:
                return False, f"关联用户ID「{user_id}」不存在（外键约束失败）"

            # 2. 构造并新增Todo
            new_todo = Todo(
                user_id=user_id,
                description=description,
                deadline=deadline,
                goal_id=goal_id,
                project_id=project_id,
                self_score=self_score,
                importance=importance,
                is_done=is_done,
                remark=remark,
                attachment=attachment,
                focus_time=focus_time,
                submit_time=datetime.now()  # 若模型已设置default=db.func.now()，可省略
            )
            db.session.add(new_todo)
            db.session.commit()
            return True, new_todo.id
        except Exception as e:
            print(str(e))
            db.session.rollback()
            return False, f"新增Todo任务失败：{str(e)}"

    @staticmethod
    def delete_todo(todo_id: int) -> (bool, str):
        """
        根据ID删除Todo任务
        :param todo_id: 任务ID
        :return: (bool, 提示字符串)
        """
        try:
            # 1. 查询任务是否存在
            todo = db.session.query(Todo).filter(Todo.id == todo_id).first()
            if not todo:
                return False, f"Todo任务ID「{todo_id}」不存在"

            # 2. 删除任务（无关联表约束，直接删除）
            db.session.delete(todo)
            db.session.commit()
            return True, f"Todo任务ID「{todo_id}」删除成功"
        except Exception as e:
            db.session.rollback()
            return False, f"删除Todo任务失败：{str(e)}"

    @staticmethod
    def update_todo(
            todo_id: int,
            description: Optional[str] = None,
            deadline: Optional[date] = None,
            goal_id: Optional[int] = None,
            project_id: Optional[int] = None,
            self_score: Optional[int] = None,
            importance: Optional[int] = None,
            is_done: Optional[int] = None,
            remark: Optional[str] = None,
            attachment: Optional[str] = None,
            focus_time: Optional[int] = None
    ) -> (bool, str):
        """
        更新Todo任务（支持部分字段更新）
        :param todo_id: 任务ID（必传）
        :param 其他参数：可选更新字段（外键需校验存在性）
        :return: (bool, 提示字符串)
        """
        try:
            # 1. 查询任务是否存在
            todo = db.session.query(Todo).filter(Todo.id == todo_id).first()
            if not todo:
                return False, f"Todo任务ID「{todo_id}」不存在"

            # 2. 逐个更新字段（外键需先校验）
            if description is not None:
                todo.description = description
            if deadline is not None:
                todo.deadline = deadline
            if goal_id is not None:
                goal_exists = db.session.query(
                    db.exists().where(db.Model.metadata.tables['goal'].c.id == goal_id)).scalar()
                if not goal_exists:
                    return False, f"关联目标ID「{goal_id}」不存在（外键约束失败）"
                todo.goal_id = goal_id
            if project_id is not None:
                # 允许project_id设为None（可选字段）
                if project_id is not None:
                    project_exists = db.session.query(
                        db.exists().where(db.Model.metadata.tables['program'].c.id == project_id)).scalar()
                    if not project_exists:
                        return False, f"关联项目ID「{project_id}」不存在（外键约束失败）"
                todo.project_id = project_id
            if self_score is not None:
                todo.self_score = self_score
            if importance is not None:
                todo.importance = importance
            if is_done is not None:
                if is_done not in [0, 1]:
                    return False, f"完成状态「{is_done}」无效，仅支持0（待完成）/1（已完成）"
                todo.is_done = is_done
            if remark is not None:
                todo.remark = remark
            if attachment is not None:
                todo.attachment = attachment
            if focus_time is not None:
                todo.focus_time = focus_time

            # 3. 提交更新
            db.session.commit()
            return True, f"Todo任务ID「{todo_id}」更新成功"
        except Exception as e:
            db.session.rollback()
            return False, f"更新Todo任务失败：{str(e)}"

    @staticmethod
    def get_todo_by_id(todo_id: int):
        """
        根据ID查询单个Todo任务（含外键对应的name字段）
        :param todo_id: 任务ID
        :return: (bool, 结果字典/提示字符串)
        """
        try:
            # 关联查询：todo + user_info（用户名） + goal（目标名） + program（项目名，可选）
            todo = db.session.query(
                Todo,
                db.Model.metadata.tables['user_info'].c.username.label('username'),  # 用户名称
                db.Model.metadata.tables['goal'].c.goal_name.label('goal_name'),  # 目标名称
                db.Model.metadata.tables['program'].c.program_name.label('program_name')  # 项目名称
            ).join(
                db.Model.metadata.tables['user_info'],
                Todo.user_id == db.Model.metadata.tables['user_info'].c.id,
            ).join(
                db.Model.metadata.tables['goal'],
                Todo.goal_id == db.Model.metadata.tables['goal'].c.id,
            ).outerjoin(
                db.Model.metadata.tables['program'],
                Todo.project_id == db.Model.metadata.tables['program'].c.id,
            ).filter(
                Todo.id == todo_id
            ).first()

            if not todo:
                return False, f"Todo任务ID「{todo_id}」不存在"

            # 解构查询结果（todo_obj 是Todo实例，其余是关联字段）
            todo_obj, username, goal_name, program_name = todo

            # 构造返回字典（含所有字段 + 外键name）
            result = {
                "id": todo_obj.id,
                "user_id": todo_obj.user_id,
                "username": username or "",  # 用户名（外键关联）
                "description": todo_obj.description,
                "deadline": todo_obj.deadline.strftime("%Y-%m-%d") if todo_obj.deadline else "",  # date转字符串
                "goal_id": todo_obj.goal_id,
                "goal_name": goal_name or "",  # 目标名称（外键关联）
                "project_id": todo_obj.project_id,
                "program_name": program_name or "无",  # 项目名称（外键关联，可选）
                "self_score": todo_obj.self_score,
                "importance": todo_obj.importance,
                "is_done": todo_obj.is_done,
                "remark": todo_obj.remark,
                "attachment": todo_obj.attachment or "无",
                "submit_time": todo_obj.submit_time.strftime("%Y-%m-%d %H:%M:%S") if todo_obj.submit_time else "",
                # datetime转字符串
                "focus_time": todo_obj.focus_time
            }
            return True, result
        except Exception as e:
            return False, f"查询Todo任务失败：{str(e)}"

    @staticmethod
    def get_all_todos(
            user_id: Optional[int] = None,
            is_done: Optional[int] = None,
            goal_id: Optional[int] = None,
            deadline: Optional[str] = None,  # 改为 Optional[str]，明确支持 None
            deadline_start: Optional[str] = None,  # 新增：截止日期起始（支持范围查询）
            deadline_end: Optional[str] = None  # 新增：截止日期结束（支持范围查询）
    ):  # 补充返回值类型注解
        """
        查询所有Todo任务（支持按用户ID、完成状态、目标ID、截止日期（精确/范围）筛选，含外键name）
        :param user_id: 筛选用户ID（可选）
        :param is_done: 筛选完成状态（0/1，可选）
        :param goal_id: 筛选目标ID（可选）
        :param deadline: 截止日期（精确匹配，格式：YYYY-MM-DD，可选）
        :param deadline_start: 截止日期起始（范围查询，格式：YYYY-MM-DD，可选）
        :param deadline_end: 截止日期结束（范围查询，格式：YYYY-MM-DD，可选）
        :return: (bool, 结果列表/提示字符串)
        """
        try:
            # 基础查询：user_info和goal用左连接（避免数据丢失），program用左连接（可选字段）
            query = db.session.query(
                Todo,
                db.Model.metadata.tables['user_info'].c.username.label('username'),
                db.Model.metadata.tables['goal'].c.goal_name.label('goal_name'),
                db.Model.metadata.tables['program'].c.program_name.label('program_name')
            ).outerjoin(  # 改为 outerjoin：即使无关联用户/目标（极端情况），也保留Todo记录
                db.Model.metadata.tables['user_info'],
                Todo.user_id == db.Model.metadata.tables['user_info'].c.id,
            ).outerjoin(
                db.Model.metadata.tables['goal'],
                Todo.goal_id == db.Model.metadata.tables['goal'].c.id,
            ).outerjoin(
                db.Model.metadata.tables['program'],
                Todo.project_id == db.Model.metadata.tables['program'].c.id,
            )

            # 条件筛选：逐个校验 + 安全过滤
            if user_id is not None:
                if not isinstance(user_id, int) or user_id <= 0:
                    return False, f"筛选条件「user_id={user_id}」无效，需为正整数"
                query = query.filter(Todo.user_id == user_id)

            if is_done is not None:
                if is_done not in [0, 1]:
                    return False, f"筛选条件「is_done={is_done}」无效，仅支持0（待完成）/1（已完成）"
                query = query.filter(Todo.is_done == is_done)

            if goal_id is not None:
                if not isinstance(goal_id, int) or goal_id <= 0:
                    return False, f"筛选条件「goal_id={goal_id}」无效，需为正整数"
                query = query.filter(Todo.goal_id == goal_id)

            # 处理截止日期筛选（精确匹配 + 范围匹配，互斥）
            deadline_filters = []
            # 1. 精确匹配 deadline
            if deadline is not None:
                if deadline_start or deadline_end:
                    return False, "截止日期筛选：精确匹配（deadline）与范围匹配（deadline_start/deadline_end）不可同时使用"
                try:
                    # 校验并转换为 date 类型（确保格式正确）
                    deadline_date = datetime.strptime(deadline, "%Y-%m-%d").date()
                    deadline_filters.append(Todo.deadline == deadline_date)
                except ValueError:
                    return False, f"截止日期「{deadline}」格式无效，需为 YYYY-MM-DD（如 2025-12-31）"

            # 2. 范围匹配 deadline_start/deadline_end
            else:
                if deadline_start is not None:
                    try:
                        start_date = datetime.strptime(deadline_start, "%Y-%m-%d").date()
                        deadline_filters.append(Todo.deadline >= start_date)
                    except ValueError:
                        return False, f"截止日期起始「{deadline_start}」格式无效，需为 YYYY-MM-DD"
                if deadline_end is not None:
                    try:
                        end_date = datetime.strptime(deadline_end, "%Y-%m-%d").date()
                        deadline_filters.append(Todo.deadline <= end_date)
                    except ValueError:
                        return False, f"截止日期结束「{deadline_end}」格式无效，需为 YYYY-MM-DD"
                # 校验起始日期 <= 结束日期
                if deadline_start and deadline_end and start_date > end_date:
                    return False, f"截止日期起始「{deadline_start}」不能晚于结束「{deadline_end}」"

            # 应用截止日期筛选条件
            if deadline_filters:
                query = query.filter(*deadline_filters)

            # 执行查询（按提交时间倒序，最新的任务在前）
            todos = query.order_by(Todo.submit_time.desc()).all()

            # 构造结果列表（保持原格式，优化空值处理）
            result = []
            for todo in todos:
                todo_obj, username, goal_name, program_name = todo
                result.append({
                    "id": todo_obj.id,
                    "user_id": todo_obj.user_id,
                    "username": username or "未知用户",  # 更友好的默认值
                    "description": todo_obj.description,
                    "deadline": todo_obj.deadline.strftime("%Y-%m-%d") if todo_obj.deadline else "无",
                    "goal_id": todo_obj.goal_id,
                    "goal_name": goal_name,
                    "project_id": todo_obj.project_id,
                    "program_name": program_name or "无",
                    "self_score": todo_obj.self_score,
                    "importance": todo_obj.importance,
                    "is_done": int(todo_obj.is_done),
                    "remark": todo_obj.remark,
                    "attachment": todo_obj.attachment or "无",
                    "submit_time": todo_obj.submit_time.strftime("%Y-%m-%d %H:%M:%S") if todo_obj.submit_time else "无",
                    "focus_time": todo_obj.focus_time
                })
            return True, result
        except Exception as e:
            # 捕获异常时输出详细信息，方便排查
            return False, f"查询所有Todo任务失败：{str(e)}（参数：user_id={user_id}, is_done={is_done}, goal_id={goal_id}, deadline={deadline}）"

    # 根据项目id获取对应的todo信息
    @staticmethod
    def get_todo_by_project_id(project_id: int):
        """
        根据项目id获取对应的todo信息。
        :param project_id: 项目ID
        :return: (bool, 结果列表/提示字符串)
        """
        try:
            todos = db.session.query(
                Todo,
                db.Model.metadata.tables['user_info'].c.username.label('username'),
                db.Model.metadata.tables['goal'].c.goal_name.label('goal_name'),
                db.Model.metadata.tables['program'].c.program_name.label('program_name')
            ).outerjoin(
                db.Model.metadata.tables['user_info'],
                Todo.user_id == db.Model.metadata.tables['user_info'].c.id,
            ).outerjoin(
                db.Model.metadata.tables['goal'],
                Todo.goal_id == db.Model.metadata.tables['goal'].c.id,
            ).outerjoin(
                db.Model.metadata.tables['program'],
                Todo.project_id == db.Model.metadata.tables['program'].c.id,
            ).filter(
                Todo.project_id == project_id
            ).order_by(Todo.submit_time.desc()).all()

            if not todos:
                return False, f"项目ID「{project_id}」下无对应的Todo任务"

            result = []
            for todo in todos:
                todo_obj, username, goal_name, program_name = todo
                result.append({
                    "id": todo_obj.id,
                    "user_id": todo_obj.user_id,
                    "username": username or "未知用户",
                    "description": todo_obj.description,
                    "deadline": todo_obj.deadline.strftime("%Y-%m-%d") if todo_obj.deadline else "无",
                    "goal_id": todo_obj.goal_id,
                    "goal_name": goal_name,
                    "project_id": todo_obj.project_id,
                    "program_name": program_name or "无",
                    "self_score": todo_obj.self_score,
                    "importance": todo_obj.importance,
                    "is_done": int(todo_obj.is_done),
                    "remark": todo_obj.remark,
                    "attachment": todo_obj.attachment or "无",
                    "submit_time": todo_obj.submit_time.strftime("%Y-%m-%d %H:%M:%S") if todo_obj.submit_time else "无",
                    "focus_time": todo_obj.focus_time
                })
            return True, result
        except Exception as e:
            return False, f"根据项目ID查询Todo任务失败：{str(e)}"